using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FreshCommonUtility.Dapper;
using FreshCommonUtility.SqlHelper;
using WeChatCmsCommon.EnumBusiness;
using WeChatModel.DatabaseModel;

namespace WeChatDataAccess
{
    /// <summary>
    /// 人员数据访问
    /// </summary>
    public class PersonData : BaseData<long, PersonModel>
    {
        /// <summary>
        /// 根据项目ID获取人员列表
        /// </summary>
        /// <param name="projectId">项目ID</param>
        /// <param name="userId">用户ID</param>
        /// <returns></returns>
        public List<PersonModel> GetListByProjectId(long projectId, long userId)
        {
            var where = new StringBuilder(" WHERE ProjectId = @ProjectId AND UserId = @UserId AND IsDel = @IsDel AND IsValid = @IsValid ");
            var param = new
            {
                ProjectId = projectId,
                UserId = userId,
                IsDel = FlagEnum.HadZore.GetHashCode(),
                IsValid = FlagEnum.HadOne.GetHashCode()
            };

            using (var conn = SqlConnectionHelper.GetOpenConnection())
            {
                return conn.GetList<PersonModel>(where.ToString(), param)?.OrderBy(f => f.Sort)?.ToList() ?? new List<PersonModel>();
            }
        }

        /// <summary>
        /// 根据姓名获取人员
        /// </summary>
        /// <param name="personName">人员姓名</param>
        /// <param name="userId">用户ID</param>
        /// <returns></returns>
        public PersonModel GetByName(string personName, long userId)
        {
            var where = new StringBuilder(" WHERE PersonName = @PersonName AND UserId = @UserId AND IsDel = @IsDel ");
            var param = new
            {
                PersonName = personName,
                UserId = userId,
                IsDel = FlagEnum.HadZore.GetHashCode()
            };

            using (var conn = SqlConnectionHelper.GetOpenConnection())
            {
                var list = conn.GetList<PersonModel>(where.ToString(), param);
                return list?.FirstOrDefault();
            }
        }

        /// <summary>
        /// 获取无项目的人员列表（ProjectId=0）
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <returns></returns>
        public List<PersonModel> GetUnassignedPersons(long userId)
        {
            var where = new StringBuilder(" WHERE ProjectId = @ProjectId AND UserId = @UserId AND IsDel = @IsDel AND IsValid = @IsValid ");
            var param = new
            {
                ProjectId = 0L,
                UserId = userId,
                IsDel = FlagEnum.HadZore.GetHashCode(),
                IsValid = FlagEnum.HadOne.GetHashCode()
            };

            using (var conn = SqlConnectionHelper.GetOpenConnection())
            {
                return conn.GetList<PersonModel>(where.ToString(), param)?.OrderBy(f => f.Sort)?.ToList() ?? new List<PersonModel>();
            }
        }

        /// <summary>
        /// 获取所有人员列表
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <param name="tagIds">标签ID列表（可选，用于筛选）</param>
        /// <returns></returns>
        public List<PersonModel> GetAllPersons(long userId, List<long> tagIds = null)
        {
            var where = new StringBuilder(" WHERE UserId = @UserId AND IsDel = @IsDel AND IsValid = @IsValid ");
            object param;

            if (tagIds != null && tagIds.Count > 0)
            {
                // 如果有标签筛选，需要联合person_tag表查询
                var sql = @"
                    SELECT DISTINCT p.* 
                    FROM person p 
                    INNER JOIN person_tag pt ON p.Id = pt.PersonId 
                    WHERE p.UserId = @UserId AND p.IsDel = @IsDel AND p.IsValid = @IsValid 
                    AND pt.UserId = @UserId AND pt.IsDel = @IsDel AND pt.IsValid = @IsValid 
                    AND pt.Id IN @TagIds 
                    ORDER BY p.Sort, p.CreateTime DESC";
                    
                param = new
                {
                    UserId = userId,
                    IsDel = FlagEnum.HadZore.GetHashCode(),
                    IsValid = FlagEnum.HadOne.GetHashCode(),
                    TagIds = tagIds.ToArray()
                };
                
                using (var conn = SqlConnectionHelper.GetOpenConnection())
                {
                    return conn.GetList<PersonModel>(sql, param)?.ToList() ?? new List<PersonModel>();
                }
            }
            else
            {
                // 无标签筛选，直接查询所有人员
                param = new
                {
                    UserId = userId,
                    IsDel = FlagEnum.HadZore.GetHashCode(),
                    IsValid = FlagEnum.HadOne.GetHashCode()
                };

                using (var conn = SqlConnectionHelper.GetOpenConnection())
                {
                    return conn.GetList<PersonModel>(where.ToString(), param)?.OrderBy(f => f.Sort)?.ThenByDescending(f => f.CreateTime)?.ToList() ?? new List<PersonModel>();
                }
            }
        }

        /// <summary>
        /// 保存人员信息
        /// </summary>
        /// <param name="model">人员模型</param>
        /// <returns>人员ID</returns>
        public long SaveModel(PersonModel model)
        {
            if (model == null) return 0;
            using (var conn = SqlConnectionHelper.GetOpenConnection())
            {
                if (model.Id > 0)
                {
                    // 更新
                    conn.Update(model);
                    return model.Id;
                }
                else
                {
                    // 添加
                    return conn.Insert<long, PersonModel>(model);
                }
            }
        }

        /// <summary>
        /// 删除人员
        /// </summary>
        /// <param name="id">人员ID</param>
        public void DelModel(long id)
        {
            if (id < 1) return;
            var model = Get(id);
            if (model == null) return;
            model.IsDel = FlagEnum.HadOne;
            model.UpdateTime = DateTime.Now;
            using (var conn = SqlConnectionHelper.GetOpenConnection())
            {
                conn.Update(model);
            }
        }

        /// <summary>
        /// 检查人员姓名是否存在
        /// </summary>
        /// <param name="personName">人员姓名</param>
        /// <param name="userId">用户ID</param>
        /// <param name="excludeId">排除的人员ID（编辑时使用）</param>
        /// <returns></returns>
        public bool IsPersonNameExists(string personName, long userId, long? excludeId = null)
        {
            var whereStr = new StringBuilder(" WHERE PersonName = @PersonName AND UserId = @UserId AND IsDel = @IsDel ");
            object param;

            if (excludeId.HasValue)
            {
                whereStr.Append(" AND Id != @ExcludeId ");
                param = new
                {
                    PersonName = personName,
                    UserId = userId,
                    IsDel = FlagEnum.HadZore.GetHashCode(),
                    ExcludeId = excludeId.Value
                };
            }
            else
            {
                param = new
                {
                    PersonName = personName,
                    UserId = userId,
                    IsDel = FlagEnum.HadZore.GetHashCode()
                };
            }

            using (var conn = SqlConnectionHelper.GetOpenConnection())
            {
                var count = conn.RecordCount<PersonModel>(whereStr.ToString(), param);
                return count > 0;
            }
        }

        /// <summary>
        /// 检查人员姓名是否在指定项目中存在
        /// </summary>
        /// <param name="personName">人员姓名</param>
        /// <param name="userId">用户ID</param>
        /// <param name="projectId">项目ID，0表示检查无项目的人员</param>
        /// <param name="excludeId">排除的人员ID（编辑时使用）</param>
        /// <returns></returns>
        public bool IsPersonNameExistsInProject(string personName, long userId, long projectId, long? excludeId = null)
        {
            var whereStr = new StringBuilder(" WHERE PersonName = @PersonName AND UserId = @UserId AND ProjectId = @ProjectId AND IsDel = @IsDel ");
            object param;

            if (excludeId.HasValue)
            {
                whereStr.Append(" AND Id != @ExcludeId ");
                param = new
                {
                    PersonName = personName,
                    UserId = userId,
                    ProjectId = projectId,
                    IsDel = FlagEnum.HadZore.GetHashCode(),
                    ExcludeId = excludeId.Value
                };
            }
            else
            {
                param = new
                {
                    PersonName = personName,
                    UserId = userId,
                    ProjectId = projectId,
                    IsDel = FlagEnum.HadZore.GetHashCode()
                };
            }

            using (var conn = SqlConnectionHelper.GetOpenConnection())
            {
                var count = conn.RecordCount<PersonModel>(whereStr.ToString(), param);
                return count > 0;
            }
        }

        /// <summary>
        /// 清空指定项目下所有人员的项目ID
        /// </summary>
        /// <param name="projectId">项目ID</param>
        /// <param name="userId">用户ID</param>
        public void ClearProjectIdByProjectId(long projectId, long userId)
        {
            var where = " WHERE ProjectId = @ProjectId AND UserId = @UserId AND IsDel = @IsDel ";
            var param = new
            {
                ProjectId = projectId,
                UserId = userId,
                IsDel = FlagEnum.HadZore.GetHashCode()
            };

            using (var conn = SqlConnectionHelper.GetOpenConnection())
            {
                var persons = conn.GetList<PersonModel>(where, param);
                if (persons != null && persons.Any())
                {
                    foreach (var person in persons)
                    {
                        person.ProjectId = 0;
                        person.UpdateTime = DateTime.Now;
                        conn.Update(person);
                    }
                }
            }
        }
    }
}